package Server;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import client.Item;
import client.catalog;
import client.category;
public class catalogServ {

	private Statement stmt;
	private Statement stmt1;
	public catalogServ() {
	}
		
	public Object getAllCategory(Connection con1) {
		List<category> l = new ArrayList<category>();
		try {
                 stmt = con1.createStatement();
                 ResultSet res = stmt
                                 .executeQuery("SELECT * FROM category");
                 while (res.next()) {
                         category temp = new category(Integer.toString(res.getInt(1)),res.getString(2));
                         l.add(temp);
                 }
                 res.close();
		} catch (SQLException e) {
                 e.printStackTrace();
		}
		return l;// return list with all the answers
	}
	public Object getAllType(Connection con1){
		List<Item> l1 = new ArrayList<Item>();
		try {
			stmt = con1.createStatement();
			ResultSet res = stmt
                             .executeQuery("SELECT item_num , name , category, InCatalog FROM items");
			while (res.next()) {
                     Item temp1 = new Item(res.getInt(1),res.getString(2),res.getString(3),res.getBoolean(4));
                     l1.add(temp1);
			}
			res.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return l1;// return list with all the answers
	}
	public Object getItemToCatalog(Connection con1,Object msg){
		System.out.println("catalog in the server baby");
		catalog cat = (catalog) msg;
		Boolean added = null;
		List <String> scan = (List <String>)cat.getNames();
		try { 
			for (int i=0; i<scan.size();i++){
				stmt = con1.createStatement();
				stmt.executeUpdate("UPDATE items SET inCatalog=1 WHERE name='" + scan.get(i) +"'");
			}
			added = true;
		} catch (SQLException e) {
	            e.printStackTrace();
		 	}	 
		return added;
	}
	public Object catalogDisply (Connection con1){       
		List<Item> l1 = new ArrayList<Item>();   
		try {
			stmt = con1.createStatement();
			ResultSet res = stmt
        					.executeQuery("SELECT * FROM items WHERE inCatalog=1");
			while (res.next()) {
				Item temp2 = new Item(res.getInt(1),res.getString(3),res.getString(2)
										,res.getString(4),res.getInt(5),res.getInt(7),res.getString(6),
										res.getString(8),res.getBoolean(10),res.getBoolean(11),res.getInt(12));
					//1 - item_num, 2 - name, 3 - category, 4- description, 5 - price,
					//6 - quantity, 7 - special sale, 8 - picture, 9 - inCatalog, 10 - inSale 
				l1.add(temp2);
			}
			} catch (SQLException e) {
				e.printStackTrace();
				}           
		return l1;// return list with all the answers
	}
	public Object ItemDelete (Connection con1, Object msg){
		Object b = msg;
		Boolean del = true;
		catalog cat = (catalog)b;
		try {
			stmt = con1.createStatement();
			stmt1 = con1.createStatement();
			stmt.executeUpdate("UPDATE items SET inCatalog=0 WHERE name='" + cat.getName() +"'");
			ResultSet res = stmt1
							.executeQuery("SELECT inCatalog FROM items WHERE name='" + cat.getName() +"'");
			if (res.next())
				del = res.getBoolean(1);
			} catch (SQLException e) {
				e.printStackTrace();
				}
		return del;
	}
	public Object SaleUpdate (Connection con1, Object msg){
		Object b = msg;
		Item temp4 = null;
		catalog cat = (catalog)b;
		try {
			stmt = con1.createStatement();
			stmt1 = con1.createStatement();
			stmt.executeUpdate("UPDATE items SET salePrice='"+cat.getProd().getSale()+"' WHERE name='" +cat.getProd().getName()+"'");
			ResultSet res = stmt1
							.executeQuery("SELECT inSale, salePrice  FROM items WHERE name='" + cat.getProd().getName() +"'");
			if (res.next())
				temp4 = new Item(res.getBoolean(1),res.getInt(2));
			} catch (SQLException e) {
				e.printStackTrace();
				}
		return temp4;
	}
	public Object SaleAdd (Connection con1, Object msg){
		Object b = msg;
		Item temp3 = null;
		catalog cat = (catalog)b;
		try {
			stmt = con1.createStatement();
			stmt1 = con1.createStatement();
			stmt.executeUpdate("UPDATE items SET inSale=1, salePrice='"+cat.getProd().getSale()+"' WHERE name='" +cat.getProd().getName()+"'");
			ResultSet res = stmt1
							.executeQuery("SELECT salePrice, inSale FROM items WHERE name='" + cat.getProd().getName() +"'");
			if (res.next()){
				temp3 = new Item(res.getBoolean(2),res.getInt(1));
			}
			} catch (SQLException e) {
				e.printStackTrace();
				}
		return temp3;
	}
	public Object SaleDelete (Connection con1, Object msg){
		Object b = msg;
		Boolean del = true;
		catalog cat = (catalog)b;
		try {
			stmt = con1.createStatement();
			stmt1 = con1.createStatement();
			stmt.executeUpdate("UPDATE items SET inSale=0 WHERE name='" + cat.getName() +"'");
			ResultSet res = stmt1
							.executeQuery("SELECT inSale FROM items WHERE name='" + cat.getName() +"'");
			if (res.next())
				del = res.getBoolean(1);
			} catch (SQLException e) {
				e.printStackTrace();
				}
		return del;
	}
}
